(untagged)

Standard of Excellence

Dmitry Khudorozhkov

0.00/5 (No votes)

2 Mar 2006

Learn how to integrate MS Excel functionality with your JScript/WSH scripting solutions.

Download source - 0.9 Kb

Introduction

This article (complementary to my article on MS Word automation) covers the following topics:

creating and populating an Excel document;
navigating within a Workbook and working with Workbook elements;
creating charts from existing Excel data (see also Keith Thoresz's article on inserting images);
communicating with other Office applications via the clipboard.

The main purpose of this article is to explain the basic methods of JScript-based Excel automation. It is in no way a comprehensive overview of the subject, but just a starting point in your quest. I hope it will help everyone who spent days searching for non-Visual Basic scripting samples.

This article assumes you are familiar with JScript.

General HowTo's

Startup.
- Let's start with a new Excel application instance:
```
var ExlApp = new ActiveXObject("Excel.Application");
```
  At this point, you have an empty Excel running in the background (invisible). If you wish to view the whole process, execute:
```
ExlApp.Visible = true;
```
  Beware that, in spite of the application being invisible, you'll face all the messages it generates (like "Would you like to overwrite the file?", "Do you really wish to delete a worksheet?", etc.). To suppress all warnings, thus making Excel completely silent, do the following:
```
ExlApp.DisplayAlerts = false;
```
  In this case, all actions are performed according to the default settings.
- The next step is the creation of a new workbook (in other words, a new file):
```
var WorkBook = ExlApp.Workbooks.Add();
```
  This creates a workbook with three worksheets.
- You can control the number of sheets that are created with a new workbook, through the SheetsInNewWorkbook property:
```
var ExlApp = new ActiveXObject("Excel.Application");

ExlApp.SheetsInNewWorkbook = 1; // Create only one sheet


ExlApp.Visible = true;
```
- Last step here - activate the very first sheet:
```
var Sheet = WorkBook.ActiveSheet;
```
Navigating through the sheets.
- The latest expression, by using the ActiveSheet object, brought you to, as you may have guessed, the active sheet. Eventually, you may wish to create a new sheet:
```
WorkBook.Sheets.Add()
// places new sheet before the active sheet
```
  rename a sheet:
```
WorkBook.ActiveSheet.Name = "SheetOne";
```
  activate another sheet:
```
WorkBook.Sheets("SheetTwo").Activate();
var Sheet = WorkBook.ActiveSheet;
```
  or even delete a sheet:
```
WorkBook.Sheets("SheetTwo").Delete();
```
Populating the cells.
- Conventional ("cell by cell") way of putting data into the cell is:
```
Sheet.Range("Cell-Coordinates").Value = value;
```
  For example:
```
Sheet.Range("A5").Value = "5.01.2006";
```
- Together with the Range object, you can use the Cells collection with the row/column indexes:
```
Sheet.Cells(rowIndex, columnIndex)
```
  The previous example with Range can be rewritten as follows:
```
Sheet.Cells(5, 1).Value = "5.01.2006";
```
Navigating through the cells.
Usually, you don't need to manually move the cell selection cursor to put a value in it. Nevertheless, you can make a cell an active cell, by hand:
```
Sheet.Range("A5").Activate();
```
If you need to select a range of cells (for example, to perform some clipboard operations), you should use the Select method:
```
Sheet.Range("A1:C4").Select();
```

Formatting a cell.

No one is usually satisfied with the dumb Times New Roman; to construct an eye-candy spreadsheet, you'll need to change the visual styles of some cells. First comes the Font object:

// Bold text;

Sheet.Range("A1:C4").Font.Bold = true;
// Color index (from the index table) for the text;

Sheet.Range("A1:C4").Font.ColorIndex = 43;
// Font style is/isn't italic;

Sheet.Range("A1:C4").Font.Italic = false;
// Use 'Tahoma' font;

Sheet.Range("A1:C4").Font.Name = "Tahoma";
// Size (in points) of font;

Sheet.Range("A1:C4").Font.Size = 10;
// Font is/isn't struck through with a horizontal line;

Sheet.Range("A1:C4").Font.Strikethrough = false;
// Font is/isn't formatted as subscript;

Sheet.Range("A1:C4").Font.Subscript = false;
// Font is/isn't formatted as superscript;

Sheet.Range("A1:C4").Font.Superscript = false;
// Type of underline applied to the font, if any.

Sheet.Range("A1:C4").Font.Underline = XlUnderlineStyle;

XlUnderlineStyle can be one of the following:

Underline type name	Numeric value	Meaning
`xlUnderlineStyleNone`	-4142	No underline
`xlUnderlineStyleSingle`	2	Single-line underline
`xlUnderlineStyleDouble`	-4119	Double layered underline

If you don't want to render all the text with the same style, you should apply Font changes to the Characters(StartChar, Length) collection within the Range object:

// Bold text (for characters from 5^th to 9^th);

Sheet.Range("A1:C4").Characters(5, 5).Font.Bold = true;
// Font style is/isn't italic;

Sheet.Range("A1:C4").Characters(5, 5).Font.Italic = true;
// Use 'Verdana' font;

Sheet.Range("A1:C4").Characters(5, 5).Font.Name = "Verdana";

...  // etc.

Take a note: Font and Characters objects are absolutely the same used with Word's Paragraph.

One more thing to look at is the Interior object, which allows you to change the color and the pattern of the cell's background:
```
// Color index (from the index table) for the range/cell;

Sheet.Range("A1:C4").Interior.ColorIndex = 43;
Sheet.Range("A1:C4").Interior.Pattern = XlPattern;
```
XlPattern can be one of the following:

Pattern type name Numeric value

xlPatternChecker 9

xlPatternCrissCross 16

xlPatternGrid 15

xlPatternLightDown 13

xlPatternLightUp 14

xlPatternLightHorizontal 11

xlPatternLightVertical 12
The last point of interest is the Borders collection - the four borders of a range of cells. Very useful for formatting tables:
```
// Color index (from the index table) for the border;

ActiveWorksheet.Range("B2:D4").Borders.ColorIndex = 43;
ActiveWorksheet.Range("B2:D4").Borders.LineStyle = XlLineStyle;
ActiveWorksheet.Range("B2:D4").Borders.Weight = XlBorderWeight;
```
XlLineStyle can be one of the following (style names are self-explanatory, I think):

Line style name Numeric value

xlContinuous 1

xlDash -4115

xlDot -4118

xlDashDot 4

xlDashDotDot 5

xlSlantDashDot 13

xlDouble -4119

xlLineStyleNone -4142

XlBorderWeight can be one of the following:

Weight Numeric value

xlHairline 1

xlThin 2

xlMedium -4138

xlThick 4

If you don't wish to change all the borders at a time, you can choose a single border:
```
ActiveWorksheet.Range("B2:D4").Borders(XlBordersIndex).LineStyle = 
                                                      XlLineStyle;

... // etc.
```
XlBordersIndex can be one of the following:

Weight Numeric value

xlEdgeTop 8

xlEdgeBottom 9

xlEdgeLeft 7

xlEdgeRight 10

xlDiagonalDown 5

xlDiagonalUp 6

xlInsideHorizontal 12

xlInsideVertical 11

Formatting a row/column.

You can work with entire rows and columns as if they were simple cells:

// Retrieve the row/column that contains

// the specified cell or range of cells:


// rangCol now contains the "B" column.

var rangCol = Sheet.Range("B2").EntireColumn;
// rangRow now contains the 2^nd row.

var rangRow = Sheet.Range("B2").EntireRow;

// or even:


// rangCols now contains the "B" and "C" columns.

var rangCols = Sheet.Range("B2:C4").EntireColumn;
// rangRows now contains the 2^nd,

// 3^rd and 4^th rows.

var rangRows = Sheet.Range("B2:C4").EntireRow;

// Now do what you wish:

rangCols.Font.Size = 10;

... // etc.

Building charts.

Working with charts begins with learning the ChartObjects collection. First, add a new chart object:

var chartObj = Sheet.ChartObjects.Add(chartLeftPixels, 
               chartTopPixels, chartWidthPixels, chartHeightPixels);

For example:

var rang = Sheet.Range("A1:C10");
var ch = Sheet.ChartObjects.Add(rang.Left + rang.Width, 
                                rang.Top, 350, 220);

Next, define a type of chart. Excel2000 defines ~50 types, so it's up to your artistic skills to choose the best. Search the Object Browser for the XlChartType enumeration. My favorites are:

Type name	Numeric value	Meaning
`xlBarClustered`	57	Simple horizontal bar graph
`xlBarStacked`	58	Stacked bar graph - shows the contribution of individual items into overall sum
`xlColumnClustered`	51	Histogram
`xlColumnStacked`	52	Stacked histogram
`xlPie`	5	Pie chart
`xl3DPie`	-4102	3D variant of a pie chart
`xlCylinderBarClustered`	95	Nice-looking variant of a bar graph
`xlCylinderBarStacked`	96	Nice-looking variant of a stacked bar graph

ch.Chart.ChartType = -4120;

At last, define a data source for your chart:
```
ch.Chart.SetSourceData(dataRange, where);
```
DataRange is the range of cells that contains the data for the chart. Where says where the actual chart data is (thus separating data rows and data clusters), and can be either xlColumns (numeric value: 2) or xlRows (numeric value: 1). For example:
```
var rang = Sheet.Range("A1:C10");
ch.Chart.SetSourceData(rang, 2);
```
Voila! You made it!

Working with the clipboard.
Controlling the clipboard is as easy as pressing Ctrl-C/Ctrl-V:
- If you wish to transfer text data to clipboard, use the Copy and Cut methods:
```
Sheet.Range("B2").Copy();
Sheet.Range("C2").Cut();
```
  To send a chart to the clipboard, use the CopyPicture method:
```
var ch1 = Sheet.ChartObjects.Add(ch.Left + 
              ch.Width, ch.Top, 400, 250);

...

ch1.CopyPicture();
```
- The same magic works with the PasteSpecial method:
```
Sheet.Range("C4:C5").Copy();
Sheet.Range("D4:D5").PasteSpecial();
```

Saving a workbook.

Time to put your workbook aside:

var Path = WScript.ScriptFullName;
Path = Path.substring(0, Path.lastIndexOf("\\"));

WorkBook.SaveAs(Path + "/charts.xls");

Exiting.
The easiest trick takes you out:
```
ExlApp.Quit();
```

Sample script: formatting a business report

This script creates an Excel spreadsheet, populates it, creates several charts, copies them (via clipboard) to a Word document, and, finally, saves both documents.

// Start a new instance of Microsoft Excel

var ExlApp = new ActiveXObject("Excel.Application");

// Silent-mode:

ExlApp.Visible = false;
ExlApp.DisplayAlerts = false;

var WorkBook = ExlApp.Workbooks.Add();
var Sheet = WorkBook.ActiveSheet;

Sheet.Range("A1").Value = "1.01.2006";
Sheet.Range("A2").Value = "2.01.2006";
Sheet.Range("A3").Value = "3.01.2006";
Sheet.Range("A4").Value = "4.01.2006";
Sheet.Range("A5").Value = "5.01.2006";
Sheet.Range("A6").Value = "6.01.2006";
Sheet.Range("A7").Value = "7.01.2006";
Sheet.Range("A8").Value = "8.01.2006";
Sheet.Range("A9").Value = "9.01.2006";
Sheet.Range("A10").Value = "10.01.2006";

Sheet.Range("B1").Value = 1;
Sheet.Range("B2").Value = 2;
Sheet.Range("B3").Value = 3;
Sheet.Range("B4").Value = 4;
Sheet.Range("B5").Value = 5;
Sheet.Range("B6").Value = 6;
Sheet.Range("B7").Value = 7;
Sheet.Range("B8").Value = 8;
Sheet.Range("B9").Value = 9;
Sheet.Range("B10").Value = 10;

Sheet.Range("C1").Value = 11;
Sheet.Range("C2").Value = 9.9;
Sheet.Range("C3").Value = 8.8;
Sheet.Range("C4").Value = 7.7;
Sheet.Range("C5").Value = 6.6;
Sheet.Range("C6").Value = 5.5;
Sheet.Range("C7").Value = 4.4;
Sheet.Range("C8").Value = 3.3;
Sheet.Range("C9").Value = 2.2;
Sheet.Range("C10").Value = 1.1;

var rang = Sheet.Range("A1:C10");

// First chart:

var ch = Sheet.ChartObjects.Add(rang.Left + 
         rang.Width, rang.Top, 350, 220);
ch.Chart.ChartType = -4120;
ch.Chart.SetSourceData(rang, 2);

Sheet.Range("A11").Select();

ch.CopyPicture();

// Start a new instance of Microsoft Word:

var WordApp = new ActiveXObject("Word.Application");

// Silent mode:

WordApp.Visible = false;

// Create a new Word document

WordApp.Documents.Add();

WordApp.Selection.ParagraphFormat.Alignment = 1;
WordApp.Selection.Paste();

WordApp.Selection.TypeParagraph();

// Second chart:

var ch1 = Sheet.ChartObjects.Add(ch.Left + ch.Width, 
                                 ch.Top, 400, 250);
ch1.Chart.ChartType = 95;
ch1.Chart.SetSourceData(rang, 2);

ch1.CopyPicture();

WordApp.Selection.Paste();
WordApp.Selection.TypeParagraph();

// Third chart:

var ch2 = Sheet.ChartObjects.Add(rang.Left + rang.Width, 
                                 ch.Top + ch.Height, 400, 250);
ch2.Chart.ChartType = -4100;
ch2.Chart.SetSourceData(rang, 2);

ch2.CopyPicture();

WordApp.Selection.Paste();

// Save Word document & exit:

var Path = WScript.ScriptFullName;
Path = Path.substring(0, Path.lastIndexOf("\\"));

WordApp.ActiveDocument.SaveAs(Path + "/charts.doc");
WordApp.Quit();

// Save Excel document & exit:
WorkBook.SaveAs(Path + "/charts.xls");

ExlApp.Quit();

Bonus track

For those patient enough to read till the end, here is the little bonus.

If you're scripting Word/Excel outside of the Office VBA environment, the RGB function is unavailable for you. Here are two functions, useful for performing color conversion operations. These are, actually, buried deep inside MSDN, written in VB... so here they are, the JScript versions:

  // red, green, blue - intensity of a color, value from 0 to 255.

  function RGB(red, green, blue)
  {
    return (red + (green * 256) + (blue * 65536));
  }

  // Retrieve component intensity from RGB value.

  // 

  // RGBval - value of type long to retrieve from;

  // ColorIndex - index of color to get:

  //    1 - red,

  //    2 - green,

  //    3 - blue.

  //

  function fromRGB(RGBval, ColorIndex)
  {
    // Check if Num, RGBval are valid.

    if(ColorIndex > 0 && ColorIndex < 4 && RGBval > -1 && RGBval < 16777216)
    {
      var module = 1;
      for(i = 0; i < ColorIndex - 1; i++)
        module = module * 256;

      return (Number(RGBval / module)) & 255;
    }
    else
      return 0;
  }

Now, you can use the Color property with a number of objects:

Sheet.Range("C4").Font.Color = RGB(64, 128, 192);
Sheet.Range("C5").Interior.Color = RGB(200, 150, 100);
Sheet.Range("C6").Borders.Color = RGB(100, 150, 200);

History

Date posted: March 2^nd, 2006.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Pattern type name	Numeric value
`xlPatternChecker`	9
`xlPatternCrissCross`	16
`xlPatternGrid`	15
`xlPatternLightDown`	13
`xlPatternLightUp`	14
`xlPatternLightHorizontal`	11
`xlPatternLightVertical`	12

Line style name	Numeric value
`xlContinuous`	1
`xlDash`	-4115
`xlDot`	-4118
`xlDashDot`	4
`xlDashDotDot`	5
`xlSlantDashDot`	13
`xlDouble`	-4119
`xlLineStyleNone`	-4142

Weight	Numeric value
`xlHairline`	1
`xlThin`	2
`xlMedium`	-4138
`xlThick`	4

Weight	Numeric value
`xlEdgeTop`	8
`xlEdgeBottom`	9
`xlEdgeLeft`	7
`xlEdgeRight`	10
`xlDiagonalDown`	5
`xlDiagonalUp`	6
`xlInsideHorizontal`	12
`xlInsideVertical`	11